package com.authine.cloudpivot.ext.util;

import com.alibaba.fastjson.JSONObject;
import com.authine.cloudpivot.metadata.design.dto.clientobject.domainmodel.schema.BizSchema;
import com.authine.cloudpivot.metadata.design.dto.clientobject.domainmodel.schema.Properties;
import com.authine.mvp.app.launcher.domain.BoExportModel;
import com.authine.mvp.app.launcher.domain.ExportProperty;
import com.authine.mvp.app.launcher.dto.bo.BOListDTO;
import com.authine.mvp.app.launcher.utils.ModelRuntimeException;
import lombok.extern.slf4j.Slf4j;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.ResponseEntity;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StreamUtils;
import org.springframework.web.client.RestClientException;
import org.springframework.web.client.RestTemplate;

import java.io.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

import static com.authine.mvp.app.launcher.gateway.bo.ImportExportGatewayImpl.getCellValue;


/**
 * Excel 工具类(兼容xls和xlsx)
 *
 * @Author: louis
 * @Date: 2020/8/18 9:12
 */
@Slf4j
public class ExcelUtils {

    public static final String XLS = "xls";
    public static final String XLSX = "xlsx";
    public static final DateFormat FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    public static final DateFormat FILE_FORMAT = new SimpleDateFormat("yyyyMMddHHmmss");

    /**
     * 从Excel文件获取Workbook对象
     *
     * @param excelFile Excel文件
     * @return Workbook对象
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static Workbook getWorkbookFromExcel(File excelFile) {
        Workbook workbook = null;
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(excelFile);
            if (excelFile.getName().endsWith(XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (excelFile.getName().endsWith(XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                throw new IOException("文件类型错误");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    /**
     * 获取一个新的 空的 Workbook对象
     *
     * @return
     */
    public static Workbook getNullWorkbook() {
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        return workbook;
    }

    /**
     * 创建一个sheet
     *
     * @param workbook
     * @param defaultSheetName
     */
    public static void createSheet(Workbook workbook, String defaultSheetName) {
        workbook.createSheet(defaultSheetName);
    }

    /**
     * 获取一个新的Workbook对象
     *
     * @return
     */
    public static Workbook getNewWorkbook(String defaultSheetName) {
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        workbook.createSheet(defaultSheetName);
        return workbook;
    }

    /**
     * 获取一个新的Workbook对象
     *
     * @return
     */
    public static Workbook getNewWorkbook(String defaultSheetName, int num) {
        SXSSFWorkbook workbook = new SXSSFWorkbook(num);
        //workbook.createSheet(defaultSheetName);
        return workbook;
    }

    /**
     * 把Workbook对象内容输出到Excel文件
     *
     * @param book Workbook对象
     * @param file Excel文件
     * @throws FileNotFoundException 找不到文件异常，文件已创建，实际不存在该异常
     * @throws IOException           输入输出异常
     */
    public static void writeWorkbookToFile(Workbook book, File file) {
        OutputStream outputStream = null;
        try {
            if (!file.exists()) {
                if (!file.getParentFile().exists()) {
                    file.getParentFile().mkdirs();
                }
                file.createNewFile();
            }
            outputStream = new FileOutputStream(file);
            writeWorkbookToOutputStream(book, outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 把Workbook对象输出到Excel输出流
     *
     * @param book         Workbook对象
     * @param outputStream Excel输出流
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static void writeWorkbookToOutputStream(Workbook book, OutputStream outputStream) throws IOException {
        book.write(outputStream);
    }

    /**
     * 输出数据到Workbook对象中指定页码
     *
     * @param titles 标题，写在第一行，可传null
     * @param data   数据
     * @param book   Workbook对象
     * @param page   输出数据到Workbook指定页码的页面数
     */
    public static void writeDataToWorkbook(List<String> titles, List<List<Object>> data, Workbook book, int page) {
        Sheet sheet = book.getSheetAt(page);
        Row row;
        Cell cell;
        // 设置表头
        if (null != titles && !titles.isEmpty()) {
            row = sheet.getRow(0);
            if (null == row) {
                row = sheet.createRow(0);
            }

            for (int i = 0; i < titles.size(); i++) {
                cell = row.getCell(i);
                if (null == cell) {
                    cell = row.createCell(i);
                }
                cell.setCellValue(titles.get(i));
            }
        }
        if (CollectionUtils.isEmpty(data)) {
            return;
        }
        List<Object> rowData;
        for (int i = 0; i < data.size(); i++) {
            row = sheet.getRow(i + 1);
            if (null == row) {
                row = sheet.createRow(i + 1);
            }
            rowData = data.get(i);
            if (null == rowData) {
                continue;
            }
            for (int j = 0; j < rowData.size(); j++) {
                cell = row.getCell(j);
                if (null == cell) {
                    cell = row.createCell(j);
                }
                setValue(cell, rowData.get(j));
            }
        }
    }

    public static Sheet writeTitleToWorkbook(List<String> titles, Workbook book) {
        Sheet sheet = book.createSheet();
        Row row;
        Cell cell;
        if (null != titles && !titles.isEmpty()) {
            row = sheet.getRow(0);
            if (null == row) {
                row = sheet.createRow(0);
            }

            for (int i = 0; i < titles.size(); i++) {
                cell = row.getCell(i);
                if (null == cell) {
                    cell = row.createCell(i);
                }
                cell.setCellValue(titles.get(i));
            }
        }
        return sheet;
    }

    public static void writeDataToWorkbook(List<List<Object>> data, Sheet sheet, int rowStart) {
        Row row;
        Cell cell;
        if (CollectionUtils.isEmpty(data)) {
            return;
        }
        List<Object> rowData;
        for (int i = 0; i < data.size(); i++) {
            row = sheet.getRow(i + rowStart);
            if (null == row) {
                row = sheet.createRow(i + rowStart);
            }
            rowData = data.get(i);
            if (null == rowData) {
                continue;
            }
            for (int j = 0; j < rowData.size(); j++) {
                cell = row.getCell(j);
                if (null == cell) {
                    cell = row.createCell(j);
                }
                setValue(cell, rowData.get(j));
            }
        }
    }

    public static void writeTitleWithDataToWorkbook(Workbook workbook, List<Map<String, String>> list) {
        Sheet sheet = workbook.getSheetAt(0);
        Row row;
        Cell cell;
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i);
            Map<String, String> map = list.get(i);
            if (null == map) {
                continue;
            }
            for (int j = 0; j < map.size(); j++) {
                cell = row.createCell(j);
                setValue(cell, map.get(j));
            }
        }
    }

    public static void writeTitleWithDataToWorkbook2(Workbook workbook, List<Map<String, String>> list, LinkedHashMap<String, String> columnMap, int sheetIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        Row row;
        Cell cell;
        //插入文件行名称
        row = sheet.createRow(0);
        Iterator iter = columnMap.entrySet().iterator();
        int n = 0;
        while (iter.hasNext()) {
            Map.Entry entry = (Map.Entry) iter.next();
            cell = row.createCell(n);
            setValue(cell, entry.getValue());
            n++;
        }
        cell = row.createCell(n);
        setValue(cell, "错误信息");
        //插入数据
        for (int i = 1; i < list.size() + 1; i++) {
            row = sheet.createRow(i);
            Map<String, String> map = list.get(i - 1);
            if (null == map) {
                continue;
            }
            int m = 0;
            Iterator iter2 = columnMap.entrySet().iterator();
            while (iter2.hasNext()) {
                Map.Entry entry = (Map.Entry) iter2.next();
                cell = row.createCell(m);
                setValue(cell, map.get(entry.getKey().toString()));
                m++;
            }
            cell = row.createCell(m);
            setValue(cell, map.getOrDefault("errorMessage", null));
        }
    }


    /**
     * 模板文件的表头
     *
     * @param titles          主表标题
     * @param subSchemaTitles 子表标题
     * @param subTables       主表属性对应的子表名称
     * @param page            输出数据到Workbook指定页码的页面数 默认0
     **/
    public static void writeTitleToWorkbook(List<String> titles, Map<String, List<String>> subSchemaTitles, Map<String, String> subTables, Workbook book, int page) {
        Sheet sheet = book.getSheetAt(page);
        int start;
        Cell cell1, cell2;
        Row row1 = sheet.createRow(0);
        Row row2 = sheet.createRow(1);
        CellStyle cellStyle = book.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = book.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        cellStyle.setFont(font);
        int mainTitleNum = titles.size();
        /** 主表列 **/
        for (start = 0; start < mainTitleNum; start++) {
            cell1 = row1.createCell(start);
            cell1.setCellValue(titles.get(start));
            cell1.setCellStyle(cellStyle);
            cell2 = row2.createCell(start);
            cell2.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, start, start));
        }
        /** 子表列 **/
        int mergeStart, mergeEnd;
        for (Map.Entry<String, String> entry : subTables.entrySet()) {
            List<String> subTitle = subSchemaTitles.get(entry.getKey());
            int subTitleTotalCellNum = subTitle.size();
            mergeEnd = subTitleTotalCellNum + start;
            mergeStart = start;
            for (int j = 0; start < mergeEnd; start++, j++) {
                cell1 = row1.createCell(start);
                cell2 = row2.createCell(start);
                cell1.setCellValue(entry.getValue());
                cell1.setCellStyle(cellStyle);
                cell2.setCellValue(subTitle.get(j));
                cell2.setCellStyle(cellStyle);
            }
            sheet.addMergedRegion(new CellRangeAddress(0, 0, mergeStart, mergeEnd - 1));
        }
    }

    /**
     * 创建单元格并设置样式
     **/
    public static Cell createCellWithStyle(Row row, int cellNum, Workbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);
        Cell cell = row.createCell(cellNum);
        cell.setCellStyle(cellStyle);
        /*
        cellStyle.setBorderBottom(BorderStyle.DOUBLE); //底部边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);//底部边框颜色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
*/
        return cell;
    }

    /**
     * 根据元数据获取对应字段的名称
     **/
    private static String getPropertyTitle(BizSchema bizSchema, String propertyCode) {
        String title = "";
        for (Properties bizProperty : bizSchema.getProperties()) {
            if (propertyCode != null && propertyCode.equals(bizProperty.getCode())) {
                title = bizProperty.getName();
            }
        }
        return title;
    }

    /**
     * 导出模型数据文件
     *
     * @param titles         主表标题
     * @param boList         导出的数据项
     * @param mainBizSchema  主表元数据
     * @param code2bizSchema 主表中的属性对应的子表元数据
     * @param childTables    主表中的属性对应子表的表名
     * @param boExportModel  要导出的列名
     * @param page           输出数据到Workbook指定页码的页面数 默认0
     **/
    public static void writeDataToWorkbookWithChild(BOListDTO boList, BizSchema mainBizSchema, Map<String, BizSchema> code2bizSchema, Map<String, String> childTables, BoExportModel boExportModel, Workbook wb, List<String> titles, int page) {
        Sheet sheet = wb.getSheetAt(page);
        Cell cell1, cell2;
        Row row1 = sheet.createRow(0);
        Row row2 = sheet.createRow(1);
        List<ExportProperty> exportProperties = boExportModel.getProperties();

        /** 拼接表头 **/
        int cellNum = 0;
        for (int m = 0; m < exportProperties.size(); m++) {
            String exportPropertyCode = exportProperties.get(m).getPropertyCode();
            if (code2bizSchema.keySet().contains(exportPropertyCode)) {
                BizSchema childBizSchema = code2bizSchema.get(exportPropertyCode);
                String schemaCode = childBizSchema.getCode();
                List<ExportProperty> childProperties = boExportModel.getSubSchemaExportModels().stream().filter(exportModel -> Objects.equals(schemaCode, exportModel.getSchemaCode())).findFirst().get().getProperties();
                for (int n = 0; n < childProperties.size(); n++, cellNum++) {
                    cell1 = createCellWithStyle(row1, cellNum, wb);
                    cell2 = createCellWithStyle(row2, cellNum, wb);
                    cell1.setCellValue(childTables.get(exportPropertyCode));
                    cell2.setCellValue(getPropertyTitle(childBizSchema, childProperties.get(n).getPropertyCode()));
                }
                sheet.addMergedRegion(new CellRangeAddress(0, 0, m, cellNum - 1));
            } else {
                cell1 = createCellWithStyle(row1, cellNum, wb);
                //cell2 = createCellWithStyle(row2, cellNum, cellStyle);
                cell1.setCellValue(getPropertyTitle(mainBizSchema, exportPropertyCode));
                sheet.addMergedRegion(new CellRangeAddress(0, 1, cellNum, cellNum));
                cellNum++;
            }
        }

        /** 组装数据 **/
        Map<String, Properties> propertiesMap = mainBizSchema.getProperties().stream().collect(Collectors.toMap(Properties::getCode, Function.identity()));
        Row row;
        Cell cell;
        List<Map<String, Object>> data = boList.getData();
        int rowStart = 2;
        for (int i = 0; i < data.size(); i++) {
            Map<String, Object> map = data.get(i);
            Map<String, List<Map<String, Object>>> code2data = new HashMap<>();
            Map<String, Integer> code2dataNum = new HashMap<>();
            childTables.keySet().forEach(key -> {
                code2data.put(key, ((List<Map<String, Object>>) map.get(key)));
                code2dataNum.put(key, ((List<Map<String, Object>>) map.get(key)).size());
            });
            /** 子模型中最多数据行的个数 **/
            int maxRow = code2dataNum.values().stream().max(Integer::max).get();
            for (int n = 0; n < maxRow; n++) {
                row = sheet.createRow(rowStart + n);
                int cellDataNum = 0;  /** 列号，随着每次遍历递增**/
                for (int k = 0; k < exportProperties.size(); k++) {
                    ExportProperty exportProperty = exportProperties.get(k);
                    String childExportPropertyCode = exportProperty.getPropertyCode();
                    if (code2bizSchema.keySet().contains(exportProperty.getPropertyCode())) {
                        BizSchema childBizSchema = code2bizSchema.get(childExportPropertyCode);
                        String schemaCode = childBizSchema.getCode();
                        List<ExportProperty> childProperties = boExportModel.getSubSchemaExportModels().stream().filter(exportModel -> Objects.equals(schemaCode, exportModel.getSchemaCode())).findFirst().get().getProperties();
                        int rowNum = code2dataNum.get(childExportPropertyCode);
                        if (n < rowNum) {
                            Map<String, Object> childMap = code2data.get(childExportPropertyCode).get(n);
                            for (int m = 0; m < childProperties.size(); m++, cellDataNum++) {
                                Map<String, Properties> childPropertiesMap = childBizSchema.getProperties().stream().collect(Collectors.toMap(Properties::getCode, Function.identity()));
                                cell = createCellWithStyle(row, cellDataNum, wb);
                                setValue(cell, getCellValue(childMap, childProperties.get(m), childPropertiesMap));
                            }
                        }
                    } else {
                        if (n == 0) {
                            cell = createCellWithStyle(row, cellDataNum, wb);// row.createCell(k);
                            setValue(cell, getCellValue(map, exportProperty, propertiesMap));
                        }
                        cellDataNum++;
                    }
                }
            }
            rowStart = rowStart + maxRow;
        }
    }

    public static Sheet writeTitleWithSubModelToWorkbook(BizSchema mainBizSchema, Map<String, BizSchema> code2bizSchema, Map<String, String> childTables, BoExportModel boExportModel, Workbook wb) {
        Sheet sheet = wb.createSheet();
        Cell cell1, cell2;
        Row row1 = sheet.createRow(0);
        Row row2 = sheet.createRow(1);
        /** 拼接表头 **/
        List<ExportProperty> exportProperties = boExportModel.getProperties();
        int cellNum = 0;
        for (int m = 0; m < exportProperties.size(); m++) {
            String exportPropertyCode = exportProperties.get(m).getPropertyCode();
            if (code2bizSchema.keySet().contains(exportPropertyCode)) {
                BizSchema childBizSchema = code2bizSchema.get(exportPropertyCode);
                String schemaCode = childBizSchema.getCode();
                List<ExportProperty> childProperties = boExportModel.getSubSchemaExportModels().stream().filter(exportModel -> Objects.equals(schemaCode, exportModel.getSchemaCode())).findFirst().get().getProperties();
                for (int n = 0; n < childProperties.size(); n++, cellNum++) {
                    cell1 = createCellWithStyle(row1, cellNum, wb);
                    cell2 = createCellWithStyle(row2, cellNum, wb);
                    cell1.setCellValue(childTables.get(exportPropertyCode));
                    cell2.setCellValue(getPropertyTitle(childBizSchema, childProperties.get(n).getPropertyCode()));
                }
                sheet.addMergedRegion(new CellRangeAddress(0, 0, m, cellNum - 1));
            } else {
                cell1 = createCellWithStyle(row1, cellNum, wb);
                cell1.setCellValue(getPropertyTitle(mainBizSchema, exportPropertyCode));
                sheet.addMergedRegion(new CellRangeAddress(0, 1, cellNum, cellNum));
                cellNum++;
            }
        }
        return sheet;
    }

    /**
     * 读取Excel文件第一页
     *
     * @param file Excel文件
     * @return 第一页数据集合
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static List<List<Object>> readExcelSheet(File file, int page) {
        List<List<Object>> datas = new ArrayList<>();
        List<List<Object>> data = null;
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(file);
            if (file.getName().endsWith(XLS)) {
                data = readXlsSheet(inputStream, page);
            } else if (file.getName().endsWith(XLSX)) {
                data = readXlsxSheet(inputStream, page);
            } else {
                throw new IOException("文件类型错误");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (data != null && !data.isEmpty()) {
            datas.addAll(data);
        }
        return datas;
    }

    public static List<List<Object>> readExcelSheetByInputStream(ByteArrayOutputStream inputStream, int page) {
        List<List<Object>> datas = new ArrayList<>();
        List<List<Object>> data = null;
        try {
            String type = getInputStreamFileType(new ByteArrayInputStream(inputStream.toByteArray()));
            if (type.equals(XLS)) {
                data = readXlsSheet(new ByteArrayInputStream(inputStream.toByteArray()), page);
            } else if (type.equals(XLSX)) {
                data = readXlsxSheet(new ByteArrayInputStream(inputStream.toByteArray()), page);
            } else {
                throw new IOException("文件类型错误");
            }
        } catch (IOException e) {
            log.error("readExcelSheetByInputStream IOException", e);
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                log.error("readExcelSheetByInputStream IOException", e);
            }
        }

        if (data != null && !data.isEmpty()) {
            datas.addAll(data);
        }
        return datas;
    }

    /**
     * 获取文件类型
     *
     * @param inputStream
     * @return
     * @throws IOException
     */
    public static String getInputStreamFileType(InputStream inputStream) throws IOException {
        byte[] b = new byte[3];
        inputStream.read(b, 0, b.length);
        String type = bytesToHexString(b);
        type = type.toLowerCase();
        switch (type) {
            case "504b03":
                return XLSX;
            case "d0cf11":
                return XLS;
            default:
                return "OTHER";
        }
    }

    public static String bytesToHexString(byte[] src) {
        StringBuilder stringBuilder = new StringBuilder();
        if (src == null || src.length <= 0) {
            return null;
        }
        for (int i = 0; i < src.length; i++) {
            int v = src[i] & 0xFF;
            String hv = Integer.toHexString(v);
            if (hv.length() < 2) {
                stringBuilder.append(0);
            }
            stringBuilder.append(hv);
        }
        return stringBuilder.toString();
    }

    //获取流文件类型


    /**
     * 读取xls格式Excel文件第一页
     *
     * @param inputStream Excel文件输入流
     * @return 第一页数据集合
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static List<List<Object>> readXlsSheet(InputStream inputStream, int page) throws IOException {
        Workbook workbook = new HSSFWorkbook(inputStream);
        return readExcelSheet(workbook, page);
    }

    /**
     * 读取xlsx格式Excel文件第一页
     *
     * @param inputStream Excel文件输入流
     * @return 第一页数据集合
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static List<List<Object>> readXlsxSheet(InputStream inputStream, int page) throws IOException {
        Workbook workbook = new XSSFWorkbook(inputStream);
        return readExcelSheet(workbook, page);
    }

    /**
     * 读取Workbook第一页
     *
     * @param book Workbook对象
     * @return 第一页数据集合
     */
    public static List<List<Object>> readExcelSheet(Workbook book, int page) {
        return readExcel(book, page);
    }

    /**
     * 读取指定页面的Excel
     *
     * @param book Workbook对象
     * @param page 页码
     * @return 指定页面数据集合
     */
    public static List<List<Object>> readExcel(Workbook book, int page) {
        List<List<Object>> list = new ArrayList<>();
        Sheet sheet = book.getSheetAt(page);
        if (Objects.isNull(sheet.getRow(0))) {
            throw new ModelRuntimeException("文件不能为空，请重新导入");
        }
        int totalColumns = sheet.getRow(0).getLastCellNum();
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);

            // 如果当前行为空，则加入空，保持行号一致
            if (null == row) {
                list.add(null);
                continue;
            }

            List<Object> columns = new ArrayList<>();
            for (int j = 0; j < totalColumns; j++) {
                Cell cell = row.getCell(j);
                columns.add(getValue(cell));
            }

            list.add(columns);
        }

        return list;
    }

    /**
     * 解析单元格中的值
     *
     * @param cell 单元格
     * @return 单元格内的值
     */
    private static Object getValue(Cell cell) {
        if (null == cell) {
            return null;
        }
        log.info("cellType : {}, cellValue : {}", cell.getCellType(), cell.toString());
        Object value = null;
        switch (cell.getCellType()) {
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case BLANK:
                value = null;
                break;
            case NUMERIC:
                // 日期类型，转换为日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date dateCellValue = cell.getDateCellValue();
                    log.info("date : {}", dateCellValue);
                    value = com.authine.mvp.app.launcher.utils.DateUtil.dateFormat(dateCellValue);
                }

                // 数值类型
                else {
                    double tempNum = cell.getNumericCellValue();
                    if (tempNum % 1d == 0d) {
                        value = new Double(tempNum).longValue();
                    } else {
                        value = tempNum;
                    }
                }
                break;

            default:
                value = cell.toString();
                break;
        }

        return value;
    }

    /**
     * 设置单元格值
     *
     * @param cell  单元格
     * @param value 值
     */
    public static void setValue(Cell cell, Object value) {
        if (null == cell) {
            return;
        }
        if (null == value) {
            cell.setCellValue((String) null);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue(FORMAT.format((Date) value));
        }
//        else if (value instanceof Double) {
//            cell.setCellValue((Double) value);
//        } else if (value instanceof Integer) {
//            cell.setCellValue((Integer) value);
//        } else if (value instanceof Long) {
//            cell.setCellValue((Long) value);
//        }
        else if (value instanceof List) {
            cell.setCellValue(JSONObject.toJSONString(value));
        } else {
            cell.setCellValue(value.toString());
        }

    }

    public static File getTempFolder() {
        String tempFolderPath = System.getProperty("user.home") + File.separator + "_cpv_temp";
        File tempFolder = new File(tempFolderPath);
        if (!tempFolder.exists()) {
            tempFolder.mkdirs();
        }
        return tempFolder;
    }

    public static InputStream doGetFile(String url) {
        try {
            HttpGet httpGet = new HttpGet(url);
            CloseableHttpClient httpClient = HttpClients.createDefault();
            CloseableHttpResponse response = httpClient.execute(httpGet);
            if (200 == response.getStatusLine().getStatusCode()) {
                InputStream inputStream = response.getEntity().getContent();
                return inputStream;
            }
        } catch (IOException e) {
            log.error("附件获取失败:{}", e);
            return null;
        }
        return null;
    }

    public static InputStream doGetFile2(String url) {
        InputStream inputStream = null;
        log.info("附件地址信息:{}", url);
        try {
            RestTemplate restTemplate = new RestTemplate();
            ResponseEntity<byte[]> responseEntity = restTemplate.getForEntity(url, byte[].class);
            byte[] body = responseEntity.getBody();
            inputStream = new ByteArrayInputStream(body);
        } catch (RestClientException e) {
            log.error("附件获取失败:{}", e);
        }
        return inputStream;
    }

    public static InputStream doGetFile3(RestTemplate restTemplate, String url) {
        InputStream inputStream = null;
        log.info("附件地址信息:{}", url);
        try {
            ResponseEntity<byte[]> responseEntity = restTemplate.getForEntity(url, byte[].class);
            byte[] body = responseEntity.getBody();
            inputStream = new ByteArrayInputStream(body);
        } catch (RestClientException e) {
            log.error("附件获取失败:{}", e);
        }
        return inputStream;
    }

    public static String doPostFile(String url, InputStream inputStream) throws IOException {
        if (inputStream != null) {
            RestTemplate restTemplate = new RestTemplate();
            HttpHeaders headers = new HttpHeaders();
            headers.add("Content-Type", "application/octet-stream");
            byte[] bytes = StreamUtils.copyToByteArray(inputStream);
            HttpEntity<byte[]> request = new HttpEntity<>(bytes, headers);
            // 发送请求并输出结果
            ResponseEntity<String> exchange = restTemplate.exchange(url, org.springframework.http.HttpMethod.POST, request, String.class);
            if (exchange.getStatusCode().value() == 201) {
                String body = exchange.getBody();
                JSONObject jsonObject = JSONObject.parseObject(body);
                Map map = (Map) jsonObject.get("upload");
                log.info("token:{}", map.get("token").toString());
                return map.get("token").toString();
            }
            return null;
        }
        log.info("上传文件到redmine失败，inputStream null");
        return null;
    }

    /*public static String send(String url, File file) throws IOException {
        String result = null;
        URL urlObj = new URL(url);
        HttpURLConnection con = (HttpURLConnection) urlObj.openConnection();
        con.setRequestMethod("POST"); // 设置关键值,以Post方式提交表单，默认get方式
        con.setDoInput(true);
        con.setDoOutput(true);
        con.setUseCaches(false); // post方式不能使用缓存
        // 设置请求头信息
        con.setRequestProperty("Connection", "Keep-Alive");
        con.setRequestProperty("Charset", "UTF-8");
        // 设置边界
        String BOUNDARY = "----------" + System.currentTimeMillis();
        con.setRequestProperty("Content-Type", "multipart/form-data; boundary="+ BOUNDARY);
        // 请求正文信息
        // 第一部分：
        StringBuilder sb = new StringBuilder();
        sb.append("--"); // 必须多两道线
        sb.append(BOUNDARY);
        sb.append("\r\n");
        sb.append("Content-Disposition: form-data;name=\"file\";filename=\""+ file.getName() + "\"\r\n");
        sb.append("Content-Type:application/octet-stream\r\n\r\n");
        byte[] head = sb.toString().getBytes("utf-8");
        // 获得输出流
        OutputStream out = new DataOutputStream(con.getOutputStream());
        // 输出表头
        out.write(head);
        // 文件正文部分
        // 把文件已流文件的方式 推入到url中
        DataInputStream in = new DataInputStream(new FileInputStream(file));
        int bytes = 0;
        byte[] bufferOut = new byte[1024];
        while ((bytes = in.read(bufferOut)) != -1) {
            out.write(bufferOut, 0, bytes);
        }
        in.close();
        // 结尾部分
        byte[] foot = ("\r\n--" + BOUNDARY + "--\r\n").getBytes("utf-8");// 定义最后数据分隔线
        out.write(foot);
        out.flush();
        out.close();
        StringBuffer buffer = new StringBuffer();
        BufferedReader reader = null;
        try {
            // 定义BufferedReader输入流来读取URL的响应
            reader = new BufferedReader(new InputStreamReader(con.getInputStream()));
            String line = null;
            while ((line = reader.readLine()) != null) {
                buffer.append(line);
            }
            if (result == null) {
                result = buffer.toString();
            }
        } catch (IOException e) {
            System.out.println("发送POST请求出现异常！" + e);
            e.printStackTrace();
            throw new IOException("数据读取异常");
        } finally {
            if (reader != null) {
                reader.close();
            }
        }
        return result;
    }*/

    /* public static void main2(String[] args) {
         String url = "http://8.129.109.164/api/file/download?refId=29f7014f03f64597ab570a1a096e4f35";
         InputStream inputStream = ExcelUtils.doGetFile2(url);
         //InputStream inputStream = ExcelUtils.doGetFile(url);
         ByteArrayOutputStream outputStream = cloneInputStream(inputStream);
         List<List<Object>> dataList = readExcelSheetByInputStream(outputStream, 0);
         List<List<Object>> dataList2 = readExcelSheetByInputStream(outputStream, 1);
     }

     public static void main(String[] args) throws FileNotFoundException {
         String url = "http://bucket-mvp.oss-cn-shenzhen.aliyuncs.com/93f864d65e09464a9e5549dd799860c2.xlsx";
         //InputStream inputStream = new FileInputStream(url);
         InputStream inputStream = ExcelUtils.doGetFile2(url);
         ByteArrayOutputStream outputStream = cloneInputStream(inputStream);
         List<List<Object>> dataList = readExcelSheetByInputStream(outputStream, 0);
         List<List<Object>> dataList2 = readExcelSheetByInputStream(outputStream, 1);
         System.out.println("123");
     }

     public static void main1(String[] args) throws IOException {
         String myfile = "C:\\Users\\LX\\Desktop\\人员合同信息.xlsx";
         InputStream inputStream = new FileInputStream(myfile);
         String url = "http://8.129.109.164/api/file/upload";
         //doPostFile(url,inputStream);
         //send(url,new File(myfile));
     }
 */
    public static ByteArrayOutputStream cloneInputStream(InputStream input) {
        try {
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            byte[] buffer = new byte[1024];
            int len;
            while ((len = input.read(buffer)) > -1) {
                baos.write(buffer, 0, len);
            }
            baos.flush();
            return baos;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static byte[] FileToBytes(File file) {
        byte[] buffer = null;
        try {
            ByteArrayOutputStream outputStream = cloneInputStream(new FileInputStream(file));
            buffer = outputStream.toByteArray();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        return buffer;
    }
}